SQL题1
插入数据（见课件）
SQL实现
-- 查询练习

 #1.查询所有的商品

     SELECT * FROM emp;

 #2.查询商品名和商品价格

   SELECT ename,  price FROM emp;    

 #3.别名查询.使用的关键字是as（as可以省略的）. 

    SELECT pname AS 名称,price AS 价格 FROM products;
    SELECT pname 名称,price 价格 FROM products;

 #4.查询商品价格，对价格去除重复；

    SELECT DISTINCT price FROM emp;

 #5.查询结果是表达式（运算查询）：将所有商品的价格+10元进行显示.

    SELECT pname,price+10 price FROM products;

 #6.查询商品名称为“花花公子”的商品所有信息：

    SELECT * FROM products WHERE pname = '花花公子';
    SELECT * FROM products WHERE pname IN ('花花公子');

 #7.查询价格为800商品

    SELECT * FROM products WHERE price = 800;

 #8.查询价格不是800的所有商品

    SELECT * FROM products WHERE price != 800;
    SELECT * FROM products WHERE price <> 800;

 #9.查询商品价格大于60元的所有商品信息

    SELECT * FROM products WHERE price > 60;

 #10.查询商品价格在200到1000之间所有商品

    SELECT * FROM products WHERE price BETWEEN 200 AND 1000;

 #11.查询商品价格是200或800的所有商品

    SELECT * FROM products WHERE pname LIKE '%霸%'

 #12.查询含有'霸'字的所有商品

    SELECT * FROM products WHERE pname LIKE '%霸'

 #13.查询以'香'开头的所有商品

    SELECT * FROM products WHERE pname LIKE '香%'

 #14.查询第二个字为'想'的所有商品

    SELECT * FROM products WHERE pname LIKE '_想%';

 #15.查询没有分类的商品

    SELECT * FROM products WHERE pname IS NULL;

 #16.查询有分类的商品

   SELECT * FROM products WHERE pname IS NOT NULL;

-- 排序练习：

    #1.使用价格对商品信息排序(降序)

    -- 提示：使用order by语句 

   SELECT * FROM products ORDER BY price ASC;
   SELECT * FROM products ORDER BY price;

    #2.在价格排序(降序)的基础上，以主键排序(降序)

    -- 提示：若价格相同，相同价格的数据以pid降序排序

   SELECT * FROM products ORDER BY pname DESC;

    #3.显示商品的价格(去重复)，并排序(降序)

    -- 提示：DISTINCT 关键字去重 

   SELECT DISTINCT price FROM products ORDER BY price DESC;

-- 聚合函数/分组函数练习：

    #1 查询商品的总条数(两种方式)

    -- 提示：使用count()

   SELECT count() FROM  products;

    #2 查看price商品的总价格

    -- 提示：使用sum();

   SELECT count(price) FROM products;

    #3 查看price的最大值和最小值

    -- 提示：使用max()  min()

   SELECT MAX(price),MIN(price) FROM products;

    #4 查看price的平均值

    -- 提示：使用 AVG() 

   SELECT AVG(price) FROM products;

    #5 查询价格大于200商品的总条数

    -- 提示：使用 COUNT(*)统计条数

  SELECT COUNT(*) FROM products WHERE price > 200;   

-- 分组练习：

    #1 统计各个分类商品的个数

   SELECT category_id ,COUNT(*) FROM products GROUP BY category_id ;

    #2 统计各个分类商品的个数,且只显示个数大于1的信息

   SELECT category_id ,COUNT() FROM product GROUP BY category_id HAVING COUNT() > 1;
   

SQL题2
插入数据（见课件）
SQL实现
按要求实现下面的查询功能 
# 创建员工表

create table employee (

      idint primary key auto_increment,

      namevarchar(10),

      genderchar(1),   -- 性别

      salarydouble,   -- 工资

      join_datedate,  -- 入职日期

      dept_idint,

      foreignkey (dept_id) references dept(id) -- 外键，关联部门表(部门表的主键)

)   
insert intoemployee(name,gender,salary,join_date,dept_id) values('孙悟空','男',8200,'2013-02-24',1);

insert intoemployee(name,gender,salary,join_date,dept_id) values('猪八戒','男',4600,'2010-12-02',2);

insert intoemployee(name,gender,salary,join_date,dept_id) values('唐僧','男',10000,'2008-08-08',2);

insert intoemployee(name,gender,salary,join_date,dept_id) values('白骨精','女',5000,'2015-10-07',3);

insert intoemployee(name,gender,salary,join_date,dept_id) values('蜘蛛精','女',5500,'2011-03-14',1);





1) 查询工资最高的员工是谁？

   select * fromemployee where salary = (select max(salary) from employee);

2) 查询工资小于平均工资的员工有哪些？

   selectavg(salary) from employee;
   select* from employee where salary < (select avg(salary) from employee);

3) 查询大于5000的员工，来至于哪些部门，输出部门的名字

   select dept_idfrom employee where salary >5000;

4) 查询开发部与财务部所有的员工信息，分别使用子查询和表连接实现
子查询：
   select * from employee where dept_id in(select d.id from dept d where d.name='开发部' or d.name='财务部');
表连接：
   select e.* from employee e inner join deptd on e.dept_id = d.id where d.name='开发部' or d.name='财务部';

5) 查询2011年以来入职的员工信息和部门信息，分别使用子查询和表连接实现
子查询：
   select * from employee where join_date >='2011-1-1';
   select * from dept d ,(select * from employee where join_date>='2011-1-1')  e where d.id =e.dept_id;
表连接：
   select  d.*, e.* from employee einner join dept d on e.dept_id = d.id where e.join_date >= '2011-1-1';


SQL题3

插入数据（见课件）
SQL实现
第一题

-- 1、查询平均成绩大于70分的同学的学号,姓名,和平均成绩

select sid,avg(score) from sc group by sid having avg(score) > 70;

-- 1.1 分组查询每个学生的 学号,姓名,平均分

   select a.学号, a.姓名,a.班级编号, avg(b.分数) 平均分
   from 学生信息表 a, 教学成绩表 b
   where a.学号=b.学号
   group by a.学号, a.姓名,a.班级编号
   order by 平均分 desc, 班级编号

-- 1.2 增加条件：平均成绩大于70

SELECT Sid,AVG(score) AS avg FROM sc GROUP BY Sid HAVING avg >70;

第二题

-- 2. 查询所有同学的学号、姓名、选课数、总成绩

   SELECT b.s_id,b.s_name,COUNT(a.c_id),
   SUM(case WHEN a.s_score is NULL then 0 else a.s_score END)
   FROM score as a
   RIGHT JOIN student as b ON a.s_id=b.s_id
   GROUP BY s_id,s_name

-- 2.1 需要查询两张表 student表和 student_course表
 
   select * from student, student_course;

-- 2.2 需要使用 student_id 学号字段,进行分组

select * from student group by student_id;

-- 2.3 需要使用到 count函数 sum函数
select count(*), sum(student_id) from student;



第三题

-- 3. 查询学过赵云老师课程的同学的学号、姓名

select distinct t2.sid as 学号,t2.sname as 姓名 
from score t1
inner join student t2 on t1.student_id=t2.sid
inner join course t3 on t1.course_id=t3.cid
inner join teacher t4 on t3.teacher_id=t4.tid
where t4.tname='赵云'

-- 3.1 查询赵云老师的id
elect * from score where id in (1,2,5)
-- 3.2 根据老师ID,在课程表中查询所教的课程编号

-- 3.3 将上面的子查询作为 where 后面的条件



第四题

-- 4. 查询选课 少于三门学科的学员 
 select score from sc group by sno having count(sno)>3;
-- 4.1 查询每个学生学了几门课 条件1：小于等于三门
select distinct(c.cno),c.cname from course c ,sc where sc.cno=c.cno
-- 4.2 查询 学号和姓名， 将4.1 作为临时表

SQL题4
数据库表设计
以下是我们拉勾教育平台数据库中的某几张表,为了降低难度,已经简化的表中字段 

请同学们看图分析出三张表之间的关系



1) 请设计三张表,要求如下

讲师表

 讲师ID 主键 int类型

 讲师姓名 VARCHAR类型

 讲师简介 VARCHAR类型

 讲师级别 char类型 高级讲师&首席讲师

 为讲师姓名添加索引

 

课程分类表

 课程分类ID 主键 int类型

 课程分类名称 VARCHAR类型 比如前端开发 后端开发 数据库DBA......

 课程分类描述 VARCHAR类型 

 创建时间 datatime类型

 更新时间 datatime类型

 

课程表

 课程ID 主键 int类型

 课程讲师ID 外键 用于描述课程的授课老师

 课程分类ID 外键 用于描述课程所属的分类 比如 Java课程就属于后端分类

 课程标题 VARCHAR类型 比如Java VUE PHP ......

 总课时 int类型 

 浏览数量 bigint类型

 课程状态 char 类型,  0 未发布(默认)  1 已发布

 为 课程标题字段添加索引

 为 teacher_id & subject_id,添加外键约束

插入数据（见课件）
查询需求
查询刘德华老师所教的课程属于哪个课程分类